#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    qSDb  =QSqlDatabase::addDatabase("QSQLITE");
    qSDb.setDatabaseName(":memory:");
    bool ok = qSDb.open();

    if(!ok)
        qDebug() << "Error" << endl;

    QSqlQuery qr(qSDb);
    qr.exec("PRAGMA foreign_keys=ON;");
    qr.next();

    qr.exec("create table Groupp(gruppa varchar(5));");
    qr.next();
    qr.exec("create table Facul(fac varchar(5));");
    qr.next();
    qr.exec("create table fio(surname varchar(100), name varchar(100), patronymic varchar(100),ngr int,fac int);");



    QString gruppiRTF="152,142,162,112,132,1A2,182,122,192,1B2";
    QString facul="RTF,GF,RKF,FET,FVC";

    QStringList listgruppRTF=gruppiRTF.split(",");
    QStringList listFac = facul.split(",");

    for(int i=0;i<listFac.size();i++)
    {
        qr.prepare("insert into Facul (fac) "
                   "values (:fac);");
        qr.bindValue(":gruppa",listFac[i]);
        qr.exec();
    }

    for(int i=0;i<listgruppRTF.size();i++)
    {
        qr.prepare("insert into Groupp (gruppa) "
                   "values (:gruppa);");
        qr.bindValue(":gruppa",listgruppRTF[i]);
        qr.exec();
    }

    QSqlQueryModel*  model =  new QSqlQueryModel(0);
    QTableView *view = new QTableView;
    model->setQuery("select * from Groupp");
    view->setModel(model);
    view->show();

    QSqlQueryModel*  model1 =  new QSqlQueryModel(0);
    QTableView *view1 = new QTableView;
    model1->setQuery("select * from Facul");
    view1->setModel(model1);
    view1->show();
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_pushButton_2_clicked()
{
    QSqlQuery qr(qSDb);
    QString gr = ui->lineEdit_4->text();
    QString fc = ui->lineEdit_5->text();
    int nGr = 0;
    int nFac = 0;

    qr.exec("SELECT * FROM Groupp;");
    while (qr.next())
    {
        QString name = qr.value(0).toString();
        nGr++;
        if(!QString::compare(name,gr))
            break;
    }

    qr.exec("SELECT * FROM Facul;");
    while (qr.next())
    {
        QString name = qr.value(0).toString();
        nFac++;
        if(!QString::compare(name,fc))
            break;
    }

    qr.prepare("INSERT INTO  fio(surname, name, patronymic,ngr,fac )"
             "VALUES (:surname, :name, :patronymic,:ngr,:fac)");
    qr.bindValue(":surname", ui->lineEdit->text());
    qr.bindValue(":name", ui->lineEdit_2->text());
    qr.bindValue(":patronymic", ui->lineEdit_3->text());
    qr.bindValue(":ngr", nGr);
    qr.bindValue(":fac", nFac);
    qr.exec();

    QSqlQueryModel*  model =  new QSqlQueryModel(0);
    QTableView *view = new QTableView;
    model->setQuery("select * from fio");
    view->setModel(model);
    view->show();
}

void MainWindow::on_pushButton_3_clicked()
{
    QSqlQuery qr(qSDb);
    qr.exec("delete from fio where surname='"+ui->lineEdit->text()+"' and name='"+ui->lineEdit_2->text()+"' and patronymic='"+ui->lineEdit_3->text()+"';");

    QSqlQueryModel*  model =  new QSqlQueryModel(0);
    QTableView *view = new QTableView;
    model->setQuery("select * from fio");
    view->setModel(model);
    view->show();
}

void MainWindow::on_pushButton_clicked()
{
    QSqlQuery qr(qSDb);

    QSqlQueryModel*  model =  new QSqlQueryModel(0);
    QTableView *view = new QTableView;
    model->setQuery("SELECT surname,name,patronymic,ngr,fac FROM fio WHERE surname='"+ui->lineEdit->text()+"' or name='"+ui->lineEdit_2->text()+"' or patronymic='"+ui->lineEdit_3->text()+"';");
    view->setModel(model);
    view->show();
}
